package com.fastbee.mqtt.manager;


import com.alibaba.fastjson.JSONObject;
import com.fastbee.common.enums.DeviceStatus;
import com.fastbee.common.enums.TopicType;
import com.fastbee.common.utils.gateway.mq.TopicsUtils;
import com.fastbee.iot.domain.*;
import com.fastbee.iot.service.IDeviceService;
import com.fastbee.mqtt.model.DeviceInfo;
import com.fastbee.mqtt.model.PushMessageBo;
import com.fastbee.mqttclient.PubMqttClient;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import io.netty.buffer.Unpooled;
import io.netty.handler.codec.mqtt.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.DecimalFormat;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;
import java.util.stream.Collectors;


@Component
public class MqttRemoteManager {

    @Resource
    private TopicsUtils topicsUtils;
    @Resource
    private IDeviceService deviceService;

    //获取application-prod.yml中的url
    @Value("${spring.datasource.dynamic.datasource.master.url}")
    private String BDMYSQL_URL;

    @Value("${spring.datasource.dynamic.datasource.master.username}")
    private String BDMYSQL_USER;

    @Value("${spring.datasource.dynamic.datasource.master.password}")
    private String BDMYSQL_PASSWORD;

    /**
     * true: 使用netty搭建的mqttBroker  false: 使用emq
     */
    private Boolean enabled = true;

    @Resource
    private PubMqttClient pubMqttClient;

    /**
     * 推送设备状态
     * @param serialNumber 设备
     * @param status 状态
     */
    public  void pushDeviceStatus(Long productId, String serialNumber, DeviceStatus status){
        //兼容emqx推送TCP客户端上线
        Device device = deviceService.selectDeviceNoModel(serialNumber);
        String message = "{\"status\":" + status.getType() + ",\"isShadow\":" + device.getIsShadow() + ",\"rssi\":" + device.getRssi() + "}";
        String topic = topicsUtils.buildTopic(device.getProductId(), serialNumber, TopicType.STATUS_POST);
        if (enabled){
            MqttPublishMessage publishMessage = (MqttPublishMessage) MqttMessageFactory.newMessage(
                    new MqttFixedHeader(MqttMessageType.PUBLISH, false, MqttQoS.AT_MOST_ONCE, false, 0),
                    new MqttPublishVariableHeader(topic, 0),
                    Unpooled.buffer().writeBytes(message.getBytes(StandardCharsets.UTF_8))
            );
            ClientManager.pubTopic(publishMessage);
        }else {
            //emqx直接用客户端推送
            pubMqttClient.publish(1,false,topic,message);
        }

    }

    /**
     * 公共推送消息方法
     * @param bo 消息体
     */

    // MySQL数据库连接信息
    private static final String MYSQL_URL = "jdbc:mysql://61.131.246.106:17306/jxhy?characterEncoding=UTF-8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8";
    private static final String MYSQL_USER = "szhzxadmin";
    private static final String MYSQL_PASSWORD = "Szhzx@2024#M10";
    public void pushCommon(PushMessageBo bo) throws SQLException {
        //netty版本发送
        if (enabled){
            MqttPublishMessage publishMessage = (MqttPublishMessage) MqttMessageFactory.newMessage(
                    new MqttFixedHeader(MqttMessageType.PUBLISH, false, MqttQoS.AT_MOST_ONCE, false, 0),
                    new MqttPublishVariableHeader(bo.getTopic(), 0),
                    Unpooled.buffer().writeBytes(bo.getMessage().getBytes(StandardCharsets.UTF_8))
            );
            ClientManager.pubTopic(publishMessage);
            saveToDatabase(bo);
            saveBDToDatabase(bo);
        }else {
            pubMqttClient.publish(0,false,bo.getTopic(), bo.getMessage());
        }
    }

    public void saveToDatabase(PushMessageBo bo) {
        //保存到数据库
        byte[] jsonBytes =  bo.getMessage().getBytes(StandardCharsets.UTF_8);
        String jsonString = new String(jsonBytes, StandardCharsets.UTF_8);
        ObjectMapper objectMapper = new ObjectMapper();
        try {
//            List<DeviceInfo> deviceInfos = objectMapper.readValue(jsonString, new TypeReference<List<DeviceInfo>>() {});
            List<JSONObject> deviceInfos = objectMapper.readValue(jsonString, new TypeReference<List<JSONObject>>() {});
            JSONObject properties = deviceInfos.stream().filter(deviceInfo -> "properties".equals(deviceInfo.get("id"))).findFirst().orElse(null);
            JSONObject putList = new JSONObject();
            if (properties != null) {
                String value = properties.get("value").toString();
                putList = JSONObject.parseObject(value);
            }
            JSONObject newDeviceInfo = new JSONObject();
            for (JSONObject li:deviceInfos){
                if (!li.get("id").equals("properties")){
                    newDeviceInfo.put(li.get("id").toString().replaceAll("([a-z])([A-Z])", "$1_$2").toLowerCase(), li.get("value"));
                }
            }
            List<JSONObject> deviceInfos1 = new ArrayList<>();
            for (String key : putList.keySet()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.putAll(newDeviceInfo);
                String code = deviceParameter(key, newDeviceInfo.getString("device_code"));
                Long productId = deviceProductName(newDeviceInfo.getString("device_code"));
                jsonObject.put("parmater_name", code);
                jsonObject.put("parmater_code", key);
                jsonObject.put("parmater_value", putList.get(key));
                jsonObject.put("create_at", LocalDateTime.now());
                jsonObject.put("product_id", productId);

//                jdk8根据jsonObject存储顺序将key提取成字符串，拼接
                String keyStr = jsonObject.keySet().stream().map(Object::toString).collect(Collectors.joining(","));
                String valueStr = jsonObject.values().stream().map(Object::toString).collect(Collectors.joining(","));
                JSONObject li = new JSONObject();
                li.put("keyStr", keyStr);
                li.put("valueStr", valueStr);
                deviceInfos1.add(li);
            }
            System.out.println(deviceInfos1);
            //遍历deviceInfos1，通过sql向数据库插入里面的数据
//           for (JSONObject jsonObject : deviceInfos1) {
//                String sql = "INSERT INTO whhx_device_monitor ( " +jsonObject.get("keyStr")+" ) VALUES ( " +jsonObject.get("valueStr")+" )";
//
//                try (Connection mysqlConnection = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
//                     PreparedStatement preparedStatement = mysqlConnection.prepareStatement(sql)) {
//                     preparedStatement.executeUpdate();
//                     System.out.println("Data inserted successfully."+sql);
//
//               } catch (SQLException e) {
//                   System.err.println("Error inserting data into the database: " + e.getMessage());
//               }
//            }
            for (JSONObject jsonObject : deviceInfos1) {
                // 获取字段名和值
                String keyStr = jsonObject.getString("keyStr");
                String valueStr = jsonObject.getString("valueStr");

                // 拆分字段名和值
                String[] keys = keyStr.split(",");
                String[] values = valueStr.split(",");

                // 检查字段名和值的数量是否匹配
                if (keys.length != values.length) {
                    System.err.println("Error: 字段名和值的数量不匹配!");
                    continue;
                }
                // 构建SQL语句
                String sql = "INSERT INTO whhx_device_monitor (" + String.join(", ", keys) + ") VALUES (" + String.join(", ", Collections.nCopies(keys.length, "?")) + ")";

                try (Connection mysqlConnection = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
                     PreparedStatement preparedStatement = mysqlConnection.prepareStatement(sql)) {

                    // 绑定参数
                    for (int i = 0; i < values.length; i++) {
                        String value = values[i].trim(); // 去除可能的空格
                        if (keys[i].trim().equalsIgnoreCase("monitor_time")) {
                            // 如果是时间戳字段，转换为 Timestamp
//                            long timestamp = Long.parseLong(value);
//                            java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(timestamp);
//                            preparedStatement.setTimestamp(i + 1, sqlTimestamp);
                            // 尝试解析为时间戳（毫秒级 long）
                            try {
                                long timestamp = Long.parseLong(value);
                                preparedStatement.setTimestamp(i + 1, new Timestamp(timestamp));
                            } catch (NumberFormatException e1) {
                                // 如果不是时间戳，尝试解析为日期字符串
                                try {
                                    // 支持多种日期格式（可扩展）
                                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
                                            .withZone(ZoneId.systemDefault());

                                    // 尝试解析为 LocalDateTime（无时区）或 Instant（有时区）
                                    LocalDateTime dateTime;
                                    try {
                                        // 情况1：标准日期时间字符串（如 "2023-01-01 12:00:00"）
                                        dateTime = LocalDateTime.parse(value, formatter);
                                    } catch (DateTimeParseException e2) {
                                        // 情况2：带时区的 ISO 格式（如 "2023-01-01T12:00:00Z"）
                                        dateTime = Instant.parse(value).atZone(ZoneId.systemDefault()).toLocalDateTime();
                                    }

                                    // 转换为 Timestamp
                                    preparedStatement.setTimestamp(i + 1, Timestamp.valueOf(dateTime));
                                } catch (DateTimeParseException e3) {
                                    throw new IllegalArgumentException(
                                            "Invalid datetime format for 'monitor_time'. Expected timestamp or datetime string (e.g., 'yyyy-MM-dd HH:mm:ss')."
                                    );
                                }
                            }
                        } else if (value.matches("-?\\d+(\\.\\d+)?")) {
                            // 如果是数字
                            preparedStatement.setDouble(i + 1, Double.parseDouble(value));
                        } else {
                            // 如果是字符串
                            preparedStatement.setString(i + 1, value);
                        }
                    }
                    preparedStatement.executeUpdate();
                    System.out.println("Data inserted successfully: " + sql);

                } catch (SQLException e) {
                    System.err.println("Error inserting data into the database: " + e.getMessage());
                } catch (NumberFormatException e) {
                    System.err.println("Error parsing value: " + e.getMessage());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String deviceParameter(String code ,String deviceCode) throws SQLException {
        Connection mysqlConnection = DriverManager.getConnection(BDMYSQL_URL, BDMYSQL_USER, BDMYSQL_PASSWORD);
        Statement stmt = mysqlConnection.createStatement();
//        String sql = "select * from whhx_device_parameter where code = '" + code + "' and device_code = '"+deviceCode+"'";
        String sql = "SELECT name FROM whhx_device_parameter WHERE code = '" + code + "'";
        ResultSet rs = stmt.executeQuery(sql);//简单的查询语句
        String name=null;
        while (rs.next()) {
            name = rs.getString("name");
            System.out.println(name);
        }
        return name;
    }

    public void saveBDToDatabase(PushMessageBo bo) {
        //保存到数据库
        byte[] jsonBytes =  bo.getMessage().getBytes(StandardCharsets.UTF_8);
        String jsonString = new String(jsonBytes, StandardCharsets.UTF_8);
        ObjectMapper objectMapper = new ObjectMapper();
        try {
//            List<DeviceInfo> deviceInfos = objectMapper.readValue(jsonString, new TypeReference<List<DeviceInfo>>() {});
            List<JSONObject> deviceInfos = objectMapper.readValue(jsonString, new TypeReference<List<JSONObject>>() {});
            JSONObject properties = deviceInfos.stream().filter(deviceInfo -> "properties".equals(deviceInfo.get("id"))).findFirst().orElse(null);
            JSONObject putList = new JSONObject();
            if (properties != null) {
                String value = properties.get("value").toString();
                putList = JSONObject.parseObject(value);
            }
            JSONObject newDeviceInfo = new JSONObject();
            for (JSONObject li:deviceInfos){
                if (!li.get("id").equals("properties")){
                    newDeviceInfo.put(li.get("id").toString().replaceAll("([a-z])([A-Z])", "$1_$2").toLowerCase(), li.get("value"));
                }
            }
            List<JSONObject> deviceInfos1 = new ArrayList<>();
            for (String key : putList.keySet()) {
                JSONObject jsonObject = new JSONObject();
                jsonObject.putAll(newDeviceInfo);
                String code = deviceParameter(key, newDeviceInfo.getString("device_code"));
                Long productId = deviceProductName(newDeviceInfo.getString("device_code"));
                jsonObject.put("parmater_name", code);
                jsonObject.put("parmater_code", key);
                jsonObject.put("parmater_value", putList.get(key));
                jsonObject.put("create_time", LocalDateTime.now());
                jsonObject.put("product_id", productId);

//                jdk8根据jsonObject存储顺序将key提取成字符串，拼接
                String keyStr = jsonObject.keySet().stream().map(Object::toString).collect(Collectors.joining(","));
                String valueStr = jsonObject.values().stream().map(Object::toString).collect(Collectors.joining(","));
                JSONObject li = new JSONObject();
                li.put("keyStr", keyStr);
                li.put("valueStr", valueStr);
                deviceInfos1.add(li);
            }
            System.out.println(deviceInfos1);
            //遍历deviceInfos1，通过sql向数据库插入里面的数据
            for (JSONObject jsonObject : deviceInfos1) {
                // 获取字段名和值
                String keyStr = jsonObject.getString("keyStr");
                String valueStr = jsonObject.getString("valueStr");

                // 拆分字段名和值
                String[] keys = keyStr.split(",");
                String[] values = valueStr.split(",");

                // 检查字段名和值的数量是否匹配
                if (keys.length != values.length) {
                    System.err.println("Error: 字段名和值的数量不匹配!");
                    continue;
                }
                // 构建SQL语句
                String sql = "INSERT INTO whhx_device_monitor (" + String.join(", ", keys) + ") VALUES (" + String.join(", ", Collections.nCopies(keys.length, "?")) + ")";

                try (Connection mysqlConnection = DriverManager.getConnection(BDMYSQL_URL, BDMYSQL_USER, BDMYSQL_PASSWORD);
                     PreparedStatement preparedStatement = mysqlConnection.prepareStatement(sql)) {

                    // 绑定参数
                    for (int i = 0; i < values.length; i++) {
                        String value = values[i].trim(); // 去除可能的空格
                        if (keys[i].trim().equalsIgnoreCase("monitor_time")) {
                            // 尝试解析为时间戳（毫秒级 long）
                            try {
                                long timestamp = Long.parseLong(value);
                                preparedStatement.setTimestamp(i + 1, new Timestamp(timestamp));
                            } catch (NumberFormatException e1) {
                                // 如果不是时间戳，尝试解析为日期字符串
                                try {
                                    // 支持多种日期格式（可扩展）
                                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
                                            .withZone(ZoneId.systemDefault());

                                    // 尝试解析为 LocalDateTime（无时区）或 Instant（有时区）
                                    LocalDateTime dateTime;
                                    try {
                                        // 情况1：标准日期时间字符串（如 "2023-01-01 12:00:00"）
                                        dateTime = LocalDateTime.parse(value, formatter);
                                    } catch (DateTimeParseException e2) {
                                        // 情况2：带时区的 ISO 格式（如 "2023-01-01T12:00:00Z"）
                                        dateTime = Instant.parse(value).atZone(ZoneId.systemDefault()).toLocalDateTime();
                                    }

                                    // 转换为 Timestamp
                                    preparedStatement.setTimestamp(i + 1, Timestamp.valueOf(dateTime));
                                } catch (DateTimeParseException e3) {
                                    throw new IllegalArgumentException(
                                            "Invalid datetime format for 'monitor_time'. Expected timestamp or datetime string (e.g., 'yyyy-MM-dd HH:mm:ss')."
                                    );
                                }
                            }
                        } else if (value.matches("-?\\d+(\\.\\d+)?")) {
                            // 如果是数字
                            preparedStatement.setDouble(i + 1, Double.parseDouble(value));
                        } else {
                            // 如果是字符串
                            preparedStatement.setString(i + 1, value);
                        }
                    }
                    preparedStatement.executeUpdate();
                    System.out.println("Data inserted successfully: " + sql);

                } catch (SQLException e) {
                    System.err.println("Error inserting data into the database: " + e.getMessage());
                } catch (NumberFormatException e) {
                    System.err.println("Error parsing value: " + e.getMessage());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Long deviceProductName(String deviceCode) throws SQLException {
        Connection mysqlConnection = DriverManager.getConnection(BDMYSQL_URL, BDMYSQL_USER, BDMYSQL_PASSWORD);
        Statement stmt = mysqlConnection.createStatement();
//        String sql = "select * from whhx_device_parameter where code = '" + code + "' and device_code = '"+deviceCode+"'";
        String sql = "SELECT product_id FROM iot_device WHERE serial_number = '" + deviceCode + "'";
        ResultSet rs = stmt.executeQuery(sql);//简单的查询语句
        Long productId=null;
        while (rs.next()) {
            productId = rs.getLong("product_id");
            System.out.println(productId);
        }
        return productId;
    }
}
